/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.dao;

import br.com.modelo.FuncionarioBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Felipe
 */
public class FuncionarioDAO {

    private Connection connection;

    public FuncionarioDAO() {
        this.connection = new ConnectionFactory().getConnection();
    }

    public void adiciona(FuncionarioBean fb) {
        String sql, sql2;
        sql = "insert into CADASTRO "
                + "(ID_DOC_CADASTRO,TP_DOC_CADASTRO,NM_CADASTRO,NM_ENDERECO_CADASTRO"
                + "NM_CIDADE_CADASTRO,NM_ESTADO_CADASTRO,NM_PROFISSAO_CADASTRO"
                + "DT_NASC_CADASTRO,TP_SEXO_CADASTRO,TP_ESTADOCIVIL_CADASTRO)"
                + "values(?,?,?,?,?,?,?,?,?,?)";
        sql2 = "insert into FUNCIONARIO"
                + "(CADASTRO_ID_DOC_CADASTRO,NM_CARGO,LOGIN_USUARIO,SENHA_USUARIO)"
                + "values(?,?,?,?)";

        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setInt(1, fb.getCd_doc());
            stmt.setString(2, fb.getTp_doc());
            stmt.setString(3, fb.getNome());
            stmt.setString(4, fb.getEndereco());
            stmt.setString(5, fb.getCidade());
            stmt.setString(6, fb.getEstado());
            stmt.setString(7, fb.getProfissao());
            stmt.setString(8, fb.getData_nascimento());
            stmt.setString(9, fb.getSexo());
            stmt.setString(10, fb.getEstado_civil());
            stmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        try {
            PreparedStatement stmt = connection.prepareStatement(sql2);
            stmt.setInt(1, fb.getCd_doc());
            stmt.setString(2, fb.getNm_cargo_funcionario());
            stmt.setString(3, fb.getLogin_funcionario());
            stmt.setString(4, fb.getSenha_funcionario());

            stmt.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public FuncionarioBean getFuncionario() {
         FuncionarioBean fb = new FuncionarioBean();
        try {
           
            PreparedStatement stmt = this.connection
                    .prepareStatement("SELECT * FROM CADASTRO WHERE ID_DOC_CADASTRO=?");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                fb.setCd_doc(rs.getInt("ID_DOC_CADASTRO"));
                fb.setTp_doc(rs.getString("TP_DOC_CADASTRO"));
                fb.setNome(rs.getString("NM_CADASTRO"));
                fb.setEndereco(rs.getString("NM_ENDERECO_CADASTRO"));
                fb.setCidade(rs.getString("NM_CIDADE_CADASTRO"));
                fb.setEstado(rs.getString("NM_ESTADO_CADASTRO"));
                fb.setProfissao(rs.getString("NM_PROFISSAO_CADASTRO"));
                fb.setData_nascimento(rs.getString("DT_NASC_CADASTRO"));
                fb.setSexo(rs.getString("TP_SEXO_CADASTRO"));
                fb.setEstado_civil(rs.getString("TP_ESTADOCIVIL_CADASTRO"));
                
            }
               
           
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        
        try{
        PreparedStatement stmt = this.connection
                    .prepareStatement("SELECT * FROM funcionario WHERE CADASTRO_ID_DOC_CADASTRO=?");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                fb.setCd_doc(rs.getInt("CADASTRO_ID_DOC_CADASTRO"));
                fb.setNm_cargo_funcionario(rs.getString("NM_CARGO"));
                fb.setLogin_funcionario(rs.getString("LOGIN_USUARIO"));
                fb.setSenha_funcionario(rs.getString(rs.getString("SENHA_USUARIO")));
               
                
            }
               
           
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
            return fb;
    }
    
    
     public void altera(FuncionarioBean fb) {
                String sql, sql2;
		 sql = "update CADASTRO set ID_DOC_CADASTRO=?,TP_DOC_CADASTRO=?,NM_CADASTRO=?,NM_ENDERECO_CADASTRO=?"
                + "NM_CIDADE_CADASTRO=?,NM_ESTADO_CADASTRO=?,NM_PROFISSAO_CADASTRO=?"
                + "DT_NASC_CADASTRO=?,TP_SEXO_CADASTRO=?,TP_ESTADOCIVIL_CADASTRO=?)"
                        + " where ID_DOC_CADASTRO=?";
                 sql2 ="update FUNCIONARIO set (CADASTRO_ID_DOC_CADASTRO=?,NM_CARGO=?"
                         + ",LOGIN_USUARIO=?,SENHA_USUARIO=?) where CADASTRO_ID_DOC_CADASTRO=?";
                
                
		PreparedStatement stmt;
		try {
			stmt = connection.prepareStatement(sql);

			stmt.setInt(1, fb.getCd_doc());
                        stmt.setString(2, fb.getTp_doc());
			stmt.setString(3, fb.getNome());
			stmt.setString(4, fb.getEndereco());
                        stmt.setString(5, fb.getCidade());
                        stmt.setString(6, fb.getEstado());
                        stmt.setString(7, fb.getProfissao());
                        stmt.setString(8, fb.getData_nascimento());
                        stmt.setString(9, fb.getSexo());
                        stmt.setString(10, fb.getEstado_civil());
                        
                        stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		
                     
	}
                try{
                    stmt = connection.prepareStatement(sql2);
                    stmt.setInt(1, fb.getCd_doc());
                    stmt.setString(2, fb.getNm_cargo_funcionario());
                    stmt.setString(3, fb.getLogin_funcionario());
                    stmt.setString(4, fb.getSenha_funcionario());
                } catch (SQLException e) {
			throw new RuntimeException(e);
		
                     
	}
     }
     
     public void remove(FuncionarioBean fb) {
		PreparedStatement stmt;
                try {
			stmt = connection.prepareStatement("delete from FUNCIONARIO where CADASTRO_ID_DOC_CADASTRO=?");
			stmt.setInt(1, fb.getCd_doc());
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		try {
			stmt = connection.prepareStatement("delete from CADASTRO where ID_DOC_CADASTRO=?");
			stmt.setInt(1, fb.getCd_doc());
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
                
                

	}
   
    
}
